-- @owner: opentestcase043
-- @date: 2023/2/18
-- @testpoint: 共享存储，触发器相关操作

--step1：创建源表及触发表；expect：创建成功
drop table if exists test_trigger_src_tbl;
drop table if exists test_trigger_des_tbl;
create table test_trigger_src_tbl(id1 int, id2 int, id3 int)with (segment=on);
create table test_trigger_des_tbl(id1 int, id2 int, id3 int)with (segment=on);

--step2：创建触发器函数；expect：创建成功
create or replace function tri_insert_func() returns trigger as
$$
	declare
	begin
		insert into test_trigger_des_tbl values(new.id1, new.id2, new.id3);
		return new;
	end
	$$ language plpgsql;
/
create or replace function tri_update_func() returns trigger as
$$
	declare
	begin
		update test_trigger_des_tbl set id3 = new.id3 where id1=old.id1;
		return old;
	end
	$$ language plpgsql;
/
create or replace function tri_delete_func() returns trigger as
$$
	declare
	begin
		delete from test_trigger_des_tbl where id1=old.id1;
		return old;
	end
	$$ language plpgsql;
/

--step3：创建insert触发器； expect：创建成功
create trigger insert_trigger
before insert on test_trigger_src_tbl
for each row
execute procedure tri_insert_func();
/

--step4：创建update触发器；expect：创建成功
create trigger update_trigger
after update on test_trigger_src_tbl
for each row
execute procedure tri_update_func();
/

--step5：创建delete触发器；expect：创建成功
create trigger delete_trigger
before delete on test_trigger_src_tbl
for each row
execute procedure tri_delete_func();
/

--step6：执行insert触发事件并检查触发结果；expect：成功
insert into test_trigger_src_tbl values(100,200,300);
select * from test_trigger_src_tbl;
select * from test_trigger_des_tbl;

--step7：执行update触发事件并检查触发结果；expect：成功
update test_trigger_src_tbl set id3=400 where id1=100;
select * from test_trigger_src_tbl;
select * from test_trigger_des_tbl;

--step8：执行delete触发事件并检查触发结果；expect：成功
delete from test_trigger_src_tbl where id1=100;
select * from test_trigger_src_tbl;
select * from test_trigger_des_tbl;

--step9：修改触发器；expect：修改成功
alter trigger delete_trigger on test_trigger_src_tbl rename to delete_trigger_renamed;

--step10：禁用insert_trigger触发器；expect：成功
alter table test_trigger_src_tbl disable trigger insert_trigger;

--step11：禁用当前表上所有触发器；expect：成功
alter table test_trigger_src_tbl disable trigger all;

--step12：清理环境；expect：清理成功
drop trigger insert_trigger on test_trigger_src_tbl;
drop trigger update_trigger on test_trigger_src_tbl;
drop trigger delete_trigger_renamed on test_trigger_src_tbl;
drop table test_trigger_src_tbl;
drop table test_trigger_des_tbl;
drop function tri_insert_func;
drop function tri_update_func;
drop function tri_delete_func;